package com.personal.excel.analyzer;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.personal.core.enums.HtmlTypeEnum;
import com.personal.core.htmldata.HtmlIrregularData;
import com.personal.core.htmldata.InputIrregularData;
import com.personal.core.htmldata.InputTextIrregularData;
import com.personal.core.htmldata.OutputIrregularData;
import com.personal.core.utils.CoreUtil;
import com.personal.core.utils.StringUtil;
import com.personal.dataconvert.HtmlIrregularData2Html;
import com.personal.dataconvert.util.ExcelHtmlUtil;

/**
 * 不规则Excel解析
 * @author cuibo
 *
 */
public class IrregularExcelAnalyzer
{
    /** html类型分割符 */
    private static final String TYPE_INPUT = "@INPUT@";
    private static final String TYPE_INPUTTEXT = "@INPUTTEXT@";
    
    private static final String CENTER = "center";

    private static final String LEFT = "left";

    private static final String RIGHT = "right";
    
    
    
    public static String analyseExcelSheetAsHtml(Sheet sheet) throws Exception
    {
        return analyseExcelSheetAsHtml(sheet, null);
    }
    
    /**
     * 解析Sheet页作为html
     * @param sheet
     * @param inputFlag
     * @return
     */
    public static String analyseExcelSheetAsHtml(Sheet sheet, String inputFlag) throws Exception
    {
        if (sheet == null)
        {
            return null;
        }
        List<HtmlIrregularData> datas = analyseSheet(sheet, inputFlag);
        if (CoreUtil.isEmpty(datas))
        {
            return null;
        }
        return new HtmlIrregularData2Html(datas).createHtml();
    }

    public static String analyseExcelFirstSheetAsHtml(InputStream in) throws Exception
    {
        return analyseExcelFirstSheetAsHtml(in, null);
    }
    
    /**
     * 解析文件第一个Sheet页的内容，返回内容的html字符串
     * @param in
     * @param inputFlag
     * @return
     */
    public static String analyseExcelFirstSheetAsHtml(InputStream in, String inputFlag) throws Exception
    {
        if (in == null)
        {
            return null;
        }
        Workbook book = ExcelHtmlUtil.getWorkbook(in);
        if (book == null)
        {
            return null;
        }
        List<HtmlIrregularData> datas = analyseSheet(book.getSheetAt(0), inputFlag);
        if (CoreUtil.isEmpty(datas))
        {
            return null;
        }
        return new HtmlIrregularData2Html(datas).createHtml();
    }
    
    public static Map<String, List<HtmlIrregularData>> analyseExcel(InputStream in, String... sheetNames) throws Exception
    {
        return analyseExcel(in, null, sheetNames);
    }
    
    /**
     * 解析Excel，返回每个Sheet页的单元格解析结果
     * @param in
     * @param inputFlag
     * @param sheetNames  需要解析的Sheet页名称：为空的则解析Excel中所有的数据
     * @return
     * @throws Exception 
     */
    public static Map<String, List<HtmlIrregularData>> analyseExcel(InputStream in, String inputFlag, String... sheetNames) throws Exception
    {
        if (in == null)
        {
            return null;
        }
        Workbook book = ExcelHtmlUtil.getWorkbook(in);
        if (book == null)
        {
            return null;
        }
        // 解析结果
        Map<String, List<HtmlIrregularData>> result = new LinkedHashMap<String, List<HtmlIrregularData>>();
        List<HtmlIrregularData> sheetData = null;
        Sheet sheet = null;
        for (int i = 0; i < book.getNumberOfSheets(); i++)
        {
            sheet = book.getSheetAt(i);
            if (sheet == null)
            {
                continue;
            }
            if (!CoreUtil.isEmpty(sheetNames) && !CoreUtil.arrayContains(sheetNames, sheet.getSheetName()))
            {
                continue;
            }
            sheetData = analyseSheet(sheet, inputFlag);
            if (sheetData != null)
            {
                result.put(sheet.getSheetName(), sheetData);
            }
        }
        return result;
    }
    
    /**
     * 解析Excel，返回Sheet页的单元格解析结果:作为html展现
     * @param in
     * @param sheetName
     * @return
     * @throws Exception
     */
    public static String analyseExcelSheetAsHtml(InputStream in, String sheetName) throws Exception
    {
        return analyseExcelSheetAsHtml(in, null, sheetName);
    }
    
    /**
     * 解析Excel，返回Sheet页的单元格解析结果:作为html展现
     * @param in
     * @param inputFlag
     * @param sheetName
     * @return
     * @throws Exception 
     */
    public static String analyseExcelSheetAsHtml(InputStream in, String inputFlag, String sheetName) throws Exception
    {
        if (in == null)
        {
            return null;
        }
        Workbook book = ExcelHtmlUtil.getWorkbook(in);
        if (book == null)
        {
            return null;
        }
        for (int i = 0; i < book.getNumberOfSheets(); i++)
        {
            Sheet sheet = book.getSheetAt(i);
            if (sheet == null)
            {
                continue;
            }
            if (sheet.getSheetName().equals(sheetName))
            {
                return analyseExcelSheetAsHtml(sheet, inputFlag);
            }
        }
        return null;
    }
    
    public static List<HtmlIrregularData> analyseSheet(Sheet sheet, String inputFlag)
    {
        return analyseSheetImpl(sheet, inputFlag);
    }

    public static List<HtmlIrregularData> analyseSheet(Sheet sheet)
    {
        return analyseSheetImpl(sheet, null);
    }
    
    private static List<HtmlIrregularData> analyseSheetImpl(Sheet sheet, String inputFlag)
    {
        if (sheet == null)
        {
            return null;
        }
        List<HtmlIrregularData> result = new ArrayList<HtmlIrregularData>();
        for (Row row : sheet)
        {
            for (int i = 0; i < row.getLastCellNum(); i++)
            {
                Cell cell = row.getCell(i);
                if (cell != null)
                {
                    int rowIndex = cell.getRowIndex();
                    if (ExcelHtmlUtil.isMergedRegion(sheet, cell))
                    {
                        int count = getAndSaveHtmlIrregularData(result, rowIndex, i, sheet, inputFlag);
                        if (count > 0)
                        {
                            i += count - 1;
                        }
                    } else
                    {
                        result.add(transCell2HtmlIrregularData(sheet, row, cell, rowIndex, i, 1, 1, inputFlag));
                    }
                }
            }
        }
        return result;
    }

    /**
     * 保存HtmlIrregularData并获取其
     * @param result
     * @param rowIndex
     * @param colIndex
     * @param sheet
     * @param inputFlag
     * @return
     */
    private static int getAndSaveHtmlIrregularData(List<HtmlIrregularData> result, int rowIndex, int colIndex,
            Sheet sheet, String inputFlag)
    {
        int sheetmergerCount = sheet.getNumMergedRegions();
        int colspan = 0;
        for (int i = sheetmergerCount - 1; i >= 0; i--)
        {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            if (ca != null)
            {
                int firstC = ca.getFirstColumn();
                int lastC = ca.getLastColumn();
                int firstR = ca.getFirstRow();
                int lastR = ca.getLastRow();
                if ((firstC == colIndex) && (firstR == rowIndex))
                {
                    int rowspan = lastR - firstR + 1;
                    colspan = lastC - firstC + 1;
                    Row row = sheet.getRow(firstR);
                    Cell cell = row.getCell(firstC);
                    HtmlIrregularData local = transCell2HtmlIrregularData(sheet, row, cell, rowIndex, colIndex, rowspan, colspan, inputFlag); 
                    // 需要加上跨行的高度和宽度
                    for (int j = rowIndex + 1; j < lastR + 1; j++)
                    {
                        Row localRow = sheet.getRow(j);
                        if (localRow != null)
                        {
                            local.setHeight(local.getHeight() + (int) (localRow.getHeightInPoints() + 8f));
                        }
                    }
                    for (int j = colIndex + 1; j < lastC + 1; j++)
                    {
                        local.setWidth(local.getWidth() + CoreUtil.parseInt(CoreUtil.divide(sheet.getColumnWidth(j), ExcelHtmlUtil.RECALWIDTHRATIO)));
                    }
                    result.add(local);
                    break;
                }
            }
        }
        return colspan;
    }
    
    /**
     * 单元格转HtmlIrregularData
     * @param sheet
     * @param row
     * @param cell
     * @param rowIndex
     * @param colIndex
     * @param rowSpan
     * @param colSpan
     * @param inputFlag
     * @return
     */
    private static HtmlIrregularData transCell2HtmlIrregularData(Sheet sheet, Row row, Cell cell, int rowIndex,
            int colIndex, int rowSpan, int colSpan, String inputFlag)
    {
        HtmlIrregularData result = null;
        String cellValue = ExcelHtmlUtil.getCellStringValue(cell);
        // 默认就是输出框
        if (CoreUtil.isEmpty(cellValue))
        {
            result = new OutputIrregularData();
        } else
        {
            // cb 2018 02 28 指定包含特殊标记的是输入框
            if (!CoreUtil.isEmpty(inputFlag))
            {
                if (cellValue.contains(inputFlag))
                {
                    cellValue = StringUtil.replaceFirst(cellValue, inputFlag, "");
                    result = HtmlTypeEnum.getHtmlImplByType(HtmlTypeEnum.INPUT.toString());
                } else
                {
                    result = new OutputIrregularData();
                }
            } else
            {
                if (cellValue.contains(TYPE_INPUTTEXT))
                {
                    String[] arr = CoreUtil.split(cellValue, TYPE_INPUTTEXT, 2);
                    cellValue = arr[0];
                    result = new InputTextIrregularData();
                    result.setParamKey(arr.length > 1 ? arr[1] : null);
                } else if (cellValue.contains(TYPE_INPUT))
                {
                    String[] arr = CoreUtil.split(cellValue, TYPE_INPUT, 2);
                    cellValue = arr[0];
                    result = new InputIrregularData();
                    result.setParamKey(arr.length > 1 ? arr[1] : null);
                } else 
                {
                    result = new OutputIrregularData();
                }
            }
        }
        result.setData(cellValue);
        // 单元格信息
        result.setRowIndex(rowIndex);
        result.setColIndex(colIndex);
        result.setRowSpan(rowSpan);
        result.setColSpan(colSpan);
        // 设置样式一系列信息
        // 高度
        result.setHeight((int) (row.getHeightInPoints() + 8f));
        // 宽度
        result.setWidth(CoreUtil.parseInt(CoreUtil.divide(sheet.getColumnWidth(colIndex), ExcelHtmlUtil.RECALWIDTHRATIO)));
        CellStyle cellStyle = cell.getCellStyle();
        if (cellStyle != null)
        {
            result.setAlign(convertAlignToHtml(cellStyle.getAlignment()));
            result.setValign(convertVerticalAlignToHtml(cellStyle.getVerticalAlignment()));
        }
        // 暂时忽略其他样式，后期如果需要处理。call:cuibo
        result.setStyle("");
        return result;
    }

    /**
     * 返回html的水平样式
     * @param alignment
     * @return
     */
    private static String convertAlignToHtml(short alignment)
    {
        String align = LEFT;
        switch (alignment)
        {
        case CellStyle.ALIGN_LEFT:
            align = LEFT;
            break;
        case CellStyle.ALIGN_CENTER:
            align = CENTER;
            break;
        case CellStyle.ALIGN_RIGHT:
            align = RIGHT;
            break;
        default:
            break;
        }
        return align;
    }
    
    /**
     * 返回html的垂直样式
     * @param verticalAlignment
     * @return
     */
    private static String convertVerticalAlignToHtml(short verticalAlignment)
    {
        String valign = "middle";
        switch (verticalAlignment)
        {
        case CellStyle.VERTICAL_BOTTOM:
            valign = "bottom";
            break;
        case CellStyle.VERTICAL_CENTER:
            valign = "center";
            break;
        case CellStyle.VERTICAL_TOP:
            valign = "top";
            break;
        default:
            break;
        }
        return valign;
    }
}
